﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using System.IO;
using Sio.Mdm.Entities;
using Sio.Mdm.Utils;
using FirebirdSql.Data.Isql;
using System.Data.Common;

namespace Sio.Mdm.BL.Base
{
    public class BaseFirebird<T> : BaseSQL<T> where T: class, IEntity
    {
        public BaseFirebird()
        {
        }
        public BaseFirebird(string connectionName)
            : base(connectionName)
        {
        }

        protected override System.Data.Common.DbConnection CreateConnection()
        {
            FbConnectionStringBuilder builder = new FbConnectionStringBuilder(GetConnectionString());
            
            builder.Database = Environment.ExpandEnvironmentVariables(builder.Database);
            builder.Charset = "ISO8859_2";
			
			if(builder.Database.StartsWith("~"))
			{
				builder.Database = String.Format("{0}/{1}", AppDomain.CurrentDomain.BaseDirectory, builder.Database.Replace("~/", ""));
			}

            Logger.WriteDebug("Database set to {0}", builder.Database);
            Logger.WriteDebug("Charset set to {0}", builder.Charset);
                                        
            if (!File.Exists(builder.Database))
            {
                string path = Path.GetFullPath(Path.GetDirectoryName(builder.Database));
                
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                    Logger.WriteDebug("Creating database at {0}", path);
                }

                FbConnection.CreateDatabase(builder.ToString());

                //fill test data...
                var helper = new Sio.Mdm.BL.Base.FirebirdSqlHelper(builder.ToString());
                helper.PopulateEmptyData();
            }
            
            return new FbConnection(builder.ToString());
        }

        protected override System.Data.Common.DbCommand CreateCommand()
        {
            return new FbCommand();
        }

        protected System.Data.Common.DbCommand CreateStoredProcedure(string StoredProcedureName)
        {
            var cmd = new FbCommand(StoredProcedureName);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            return cmd;
        }

        protected override void AddParameter(System.Data.Common.DbCommand command, string paramName, object paramValue)
        {
            ((FbCommand)command).Parameters.Add(paramName, paramValue);
        }
    }

    public class FirebirdSqlHelper
    {
        private string _connectionString;

        public string ConnString
        {
            get { return _connectionString; }
            set
            {
                FbConnectionStringBuilder builder = new FbConnectionStringBuilder(value);

                builder.Database = Environment.ExpandEnvironmentVariables(builder.Database);
                builder.Charset = "ISO8859_2";

                if (builder.Database.StartsWith("~"))
                {
                    builder.Database = String.Format("{0}/{1}", AppDomain.CurrentDomain.BaseDirectory, builder.Database.Replace("~/", ""));
                }

                _connectionString = builder.ToString();
            }
        }
        private Dictionary<string, string> ConnectionStrings;

        public FirebirdSqlHelper(string connString)
        {
            this.ConnString = connString;
            ConnectionStrings = new Dictionary<string, string>();
            var strings = ConnString.Split(';');
            foreach (var param in strings)
            {
                var keyvalue = param.Split('=');
                if (keyvalue.Count() == 2)
                {
                    ConnectionStrings.Add(keyvalue[0], keyvalue[1]);
                }
            }
        }

        public void PopulateSampleData()
        {
            TruncateDatabase();

            ExecuteScriptFile("metadata.sql");
            ExecuteScriptFile("EmptyData.sql");
            ExecuteScriptFile("SampleData.sql");
            ExecuteScriptFile("metadata_RI.sql");
            ExecuteScriptFile("metadata_LDAP.sql");
        }

        public void PopulateEmptyData()
        {
            TruncateDatabase();

            ExecuteScriptFile("metadata.sql");
            ExecuteScriptFile("EmptyData.sql");
            ExecuteScriptFile("metadata_RI.sql");
            ExecuteScriptFile("metadata_LDAP.sql");
        }

        public void ExecuteScriptFile(string filePath)
        {
            //~/App_Data/
            filePath = String.Format("{0}/App_Data/{1}", AppDomain.CurrentDomain.BaseDirectory, filePath);

            if (!File.Exists(filePath))
            {
                throw new ApplicationException("The script file does not exist.");
            }

            FbScript script = FbScript.LoadFromFile(filePath);
            script.Parse();

            FbConnection c = new FbConnection(ConnString);
            c.Open();

            foreach (string cmd in script.Results)
            {
                //FbBatchExecution fbe = new FbBatchExecution(c);
                //fbe.SqlStatements.Add(cmd);
                Console.WriteLine(cmd);
                ExecSQL(cmd, true);
                //fbe.Execute();
            }


            /*
            FbBatchExecution fbe = new FbBatchExecution(c);
            foreach (string cmd in script.Results)
            {
                fbe.SqlStatements.Add(cmd);
                Console.WriteLine(cmd);
            }

            fbe.Execute();
             * */
            c.Close();
        }

        /// <summary>
        /// Drop all objects inside database
        /// </summary>
        public void TruncateDatabase()
        {
            AlterToFakeProcedures();
            AlterToFakeViews();
            DropGenerators();
            DropTriggers();
            DropProcedures();
            DropGenerators();
            DropViews();
            DropConstraints();
            DropIndexes();
            DropTables();
            DropTables();
            DropIndexes();
            DropTables();
            DropTables();
            DropIndexes();
            DropTables();
            DropTables();
            DropTables();
            DropIndexes();
            DropTables();
        }

        public void CreateDatabase()
        {
            FbConnection.CreateDatabase(ConnString, true);

            /*
            Hashtable parameters = new Hashtable();
            parameters.Add("User", "SYSDBA");
            parameters.Add("Password", "masterkey");
            parameters.Add("Database", @"c:\database.fdb");
            parameters.Add("Dialect", 3);
            parameters.Add("DataSource", "localhost");
            parameters.Add("Port", "3050");
            parameters.Add("Charset", "NONE");
            parameters.Add("PageSize", "8192");
            parameters.Add("ForcedWrite", true);
            FbConnection.CreateDatabase(parameters.ToString());
             */
        }

        #region DropObjects
        private void DropGenerator(string generatorName)
        {
            var command = String.Format("Drop generator {0}", generatorName);
            ExecSQL(command, false);

        }

        private void DropGenerators()
        {
            var commands = GetResults("SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0");
            foreach (var cmd in commands)
                DropGenerator(cmd);
        }

        private void DropProcedure(string procedureName)
        {
            var command = String.Format("Drop procedure {0}", procedureName);
            ExecSQL(command, false);

        }

        private void AlterToFakeProcedure(string procedureName)
        {
            var fieldsInput = GetResults(String.Format("SELECT * FROM RDB$PROCEDURE_PARAMETERS where rdb$procedure_name = '{0}' and RDB$PARAMETER_TYPE = 0 order by RDB$PARAMETER_NUMBER", procedureName));
            var fieldsOutput = GetResults(String.Format("SELECT * FROM RDB$PROCEDURE_PARAMETERS where rdb$procedure_name = '{0}' and RDB$PARAMETER_TYPE = 1 order by RDB$PARAMETER_NUMBER", procedureName));

            var sb = new StringBuilder();


            sb.AppendFormat("CREATE OR ALTER PROCEDURE {0}  \n", procedureName);

            if (fieldsInput.Count() > 0)
                sb.Append(" (");

            int ifldCount = 0;
            foreach (var fld in fieldsInput)
            {
                if (ifldCount > 0)
                    sb.Append(", \n");
                ++ifldCount;
                sb.AppendFormat("{0} {1}", fld, "integer");

            }
            if (fieldsInput.Count() > 0)
                sb.AppendFormat(") \n");
            sb.AppendFormat("returns ( \n");
            ifldCount = 0;
            foreach (var fld in fieldsOutput)
            {
                if (ifldCount > 0)
                    sb.Append(", \n");
                ++ifldCount;
                sb.AppendFormat("{0} {1}", fld, "integer");
            }
            sb.AppendFormat(") \n");
            sb.AppendFormat("as \n");
            sb.AppendFormat("BEGIN \n");
            sb.AppendFormat("   SUSPEND; \n");
            sb.AppendFormat("END");

            ExecSQL(sb.ToString(), true);
        }

        private void AlterToFakeView(string viewName)
        {
            var fields = GetResults(String.Format("SELECT * FROM RDB$RELATION_FIELDS WHERE rdb$relation_name = '{0}'", viewName));
            var sb = new StringBuilder();


            sb.AppendFormat("CREATE OR ALTER VIEW {0}( \n", viewName);
            int ifldCount = 0;
            foreach (var fld in fields)
            {
                if (ifldCount > 0)
                    sb.Append(", \n");
                ++ifldCount;
                sb.AppendFormat("{0}", fld);
            }
            sb.AppendFormat(") \n");
            sb.AppendFormat("as \n");
            sb.AppendFormat("select ", viewName);

            ifldCount = 0;
            foreach (var fld in fields)
            {
                if (ifldCount > 0)
                    sb.Append(", \n");
                ++ifldCount;
                sb.AppendFormat("{0}", "NULL");
            }

            sb.AppendFormat(" from rdb$database");

            ExecSQL(sb.ToString(), true);
        }

        private void AlterToFakeProcedures()
        {
            var commands = GetResults("SELECT RDB$PROCEDURE_NAME FROM RDB$PROCEDURES WHERE RDB$SYSTEM_FLAG=0");
            foreach (var cmd in commands)
                AlterToFakeProcedure(cmd);
        }

        private void DropProcedures()
        {
            var commands = GetResults("SELECT RDB$PROCEDURE_NAME FROM RDB$PROCEDURES WHERE RDB$SYSTEM_FLAG=0");
            foreach (var cmd in commands)
                DropProcedure(cmd);
        }

        private void DropView(string viewName)
        {
            var command = String.Format("Drop view {0}", viewName);
            ExecSQL(command, false);
        }

        private void AlterToFakeViews()
        {
            var commands = GetResults("SELECT DISTINCT RDB$VIEW_NAME FROM RDB$VIEW_RELATIONS");

            foreach (var cmd in commands)
                AlterToFakeView(cmd);
        }

        private void DropViews()
        {
            var commands = GetResults("SELECT DISTINCT RDB$VIEW_NAME FROM RDB$VIEW_RELATIONS");

            foreach (var cmd in commands)
                DropView(cmd);
        }

        private void DropTrigger(string triggerName)
        {
            var command = String.Format("Drop trigger {0}", triggerName);
            ExecSQL(command, false);
        }

        private void DropTriggers()
        {
            var commands = GetResults("SELECT * FROM RDB$TRIGGERS WHERE RDB$SYSTEM_FLAG=0");
            foreach (var cmd in commands)
                DropTrigger(cmd);
        }

        private void DropIndex(string indexName)
        {
            var command = String.Format("Drop index {0}", indexName);
            ExecSQL(command, false);
        }

        private void DropIndexes()
        {
            var commands = GetResults("SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG=0");
            foreach (var cmd in commands)
                DropIndex(cmd);
        }

        private void DropTable(string tableName)
        {
            var command = String.Format("Drop table {0}", tableName);
            ExecSQL(command, false);
        }

        private void DropTables()
        {
            var commands = GetResults("SELECT DISTINCT RDB$RELATION_NAME FROM RDB$RELATION_FIELDS WHERE RDB$SYSTEM_FLAG=0 AND RDB$VIEW_CONTEXT IS NULL");
            foreach (var cmd in commands)
                DropTable(cmd);
        }

        private void DropTableConstraint(string tableName, string constraintName)
        {
            var command = String.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", tableName.Trim(), constraintName.Trim());
            ExecSQL(command, false);

        }

        private void DropConstraints()
        {
            var command = @"SELECT DISTINCT
                rc.RDB$CONSTRAINT_NAME AS ""constraint_name"",
                rc.RDB$RELATION_NAME AS ""on table""
                FROM RDB$RELATION_CONSTRAINTS AS rc
                LEFT JOIN RDB$REF_CONSTRAINTS refc ON rc.RDB$CONSTRAINT_NAME = refc.RDB$CONSTRAINT_NAME
                LEFT JOIN RDB$DEPENDENCIES d1 ON d1.RDB$DEPENDED_ON_NAME = rc.RDB$RELATION_NAME
                LEFT JOIN RDB$DEPENDENCIES d2 ON d1.RDB$DEPENDENT_NAME = d2.RDB$DEPENDENT_NAME
                WHERE rc.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
                AND d1.RDB$DEPENDED_ON_NAME <> d2.RDB$DEPENDED_ON_NAME
                AND d1.RDB$FIELD_NAME <> d2.RDB$FIELD_NAME";

            var commands = GetMyTupple2(command);

            foreach (var dr in commands)
            {
                DropTableConstraint(dr.Item2, dr.Item1);
            }
        }
        #endregion

        #region HelperFunctions
        private void ExecSQL(string sqlStatement, bool catchError)
        {
            if (sqlStatement != "")

                //nekatere ukaze preskočim
                if (sqlStatement.Equals("COMMIT WORK") || sqlStatement.Contains("DESCRIBE PROCEDURE"))
                    return;

            using (var conn = CreateConnection())
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sqlStatement;
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    if (catchError)
                        throw;
                }

                //Debug.WriteLine(sqlStatement);
            }
        }

        private List<string> GetResults(string command)
        {
            var result = new List<string>();
            using (var conn = CreateConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = command;

                conn.Open();

                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result.Add(dr.GetString(0).Trim());
                    }
                }
            }
            return result;
        }

        private List<MyTuple<string, string>> GetMyTupple2(string command)
        {
            var result = new List<MyTuple<string, string>>();
            using (var conn = CreateConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandText = command;

                conn.Open();

                using (var dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result.Add(new MyTuple<string, string>(dr.GetString(0).Trim(), dr.GetString(1).Trim()));
                    }
                }
            }
            return result;
        }

        protected DbConnection CreateConnection()
        {
            return new FirebirdSql.Data.FirebirdClient.FbConnection(ConnString);
        }

        public void FullRecreateDatabase()
        {
            var database = ConnectionStringParser.GetParameterValue(ConnString, "Database");

            try
            {
                if (File.Exists(database))
                    File.Delete(database);
                FbConnection.CreateDatabase(ConnString, true);
            }
            catch (Exception e)
            {
                throw new NotImplementedException(String.Format("Unable to recreate database '{0}'. Reason: {1}", database, e.Message), e);
            }
        }
        #endregion
    }

    public class MyTuple<T1, T2>
    {
        private T1 _item1;
        private T2 _item2;

        public MyTuple(T1 item1, T2 item2)
        {
            this._item1 = item1;
            this._item2 = item2;
        }

        public T1 Item1 { get { return _item1; } }
        public T2 Item2 { get { return _item2; } }

    }

    public static class ConnectionStringParser
    {
        public static string GetParameterValue(string source, string key)
        {
            var idx1 = source.IndexOf(key + "=");
            if (idx1 < 0)
            {
                idx1 = source.IndexOf(key + " ");
                if (idx1 < 0)
                {
                    return null;
                }

                var idx = source.IndexOf("=", idx1 + key.Length);
                if (idx < 0)
                {
                    return null;
                }

                if (source.Substring(idx1, idx - idx1).TrimEnd() != key)
                {
                    return null;
                }

                idx1 = idx + 1;
            }
            else
            {
                idx1 += key.Length + 1;
            }

            var idx2 = source.IndexOf(";", idx1);
            if (idx2 < 0)
            {
                idx2 = source.Length;
            }

            return source.Substring(idx1, idx2 - idx1).Trim();
        }
    }
}
